using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using UDS.Management.Data.Job.Generator;
using UDS.Management.Data.Package;
using UDS.Management.Data.SchemaManagement;
using UDS.Management.Data.Deployment;

namespace UDS.Management.Data
{
    public class Database
    {
        private const int MAX_RETRIES = 10;
        private string _ServerName;
        private string _ConnectionString;
        private string _DatabaseName;
        private EmbeddedSqlScriptReader scriptReader;

        #region embedded classes

        [Flags]
        public enum DatabaseUpdateContext : int
        {
            Information = 1,
            Configure = (Information << 1),
            PreInstall = (Configure << 1),
            Replication = (PreInstall << 1),
            Schema = (Replication << 1),
            StoredProcedures = (Schema << 1),
            Views = (StoredProcedures << 1),
            Functions = (Views << 1),
            Jobs = (Functions << 1),
            PostInstall = (Jobs << 1),
            Publish = (PostInstall << 1),
            Unpublish = (Publish << 1),
            Cleanup = (Unpublish << 1),
            GenerateSQLJobs = (Cleanup << 1)
        }

        public class DatabaseUpdateEvent
        {
            public DatabaseUpdateEvent(DatabaseUpdateContext context, string name)
            {
                Context = context;
                Name = name;
            }

            public DatabaseUpdateContext Context;
            public string Name;
        };

        public delegate void DatabaseUpdateDelegate(object sender, DatabaseUpdateEvent update);


        #endregion

        /// <summary>
        /// Property - get or set the timeout for executing commands
        /// </summary>
        public int CommandTimeOut = 500;

        /// <summary>
        /// Property - get the master Connection string for this instance
        /// </summary>
        public string MasterDBConnectionString
        {
            get
            {
                return String.Format("server={0};database=master;Integrated Security=SSPI;Pooling='false';", _ServerName);
            }
        }

        public event DatabaseUpdateDelegate OnUpdate;
        protected void FireOnUpdate(DatabaseUpdateContext context, string name)
        {
            if (OnUpdate != null)
            {
                OnUpdate(this, new DatabaseUpdateEvent(context, name));
            }
        }

        //==================================================
        // Constructors
        //==================================================

        /// <summary>
        /// Create a database object that connects to the master 
        /// database on the server specified
        /// </summary>
        /// <param name="Server">The server to connect to</param>
        public Database(string Server)
        {
            _ServerName = Server;
            this._ConnectionString = MasterDBConnectionString;
            scriptReader = new EmbeddedSqlScriptReader();
        }

        /// <summary>
        /// Create a database object that connects to the database 
        /// on the server specified.
        /// </summary>
        /// <param name="Server">The server to connect to</param>
        /// <param name="DatabaseName">The database to connect to</param>
        public Database(string Server, string DatabaseName)
        {
            this._DatabaseName = DatabaseName;
            this._ServerName = Server;
            this._ConnectionString = String.Format("server={0};database={1};Integrated Security=true;Pooling=true", Server, DatabaseName);
            scriptReader = new EmbeddedSqlScriptReader();
        }

        /// <summary>
        /// Create a database object that connects to the database 
        /// on the server specified.
        /// </summary>
        /// <param name="csBuilder">Contains the connection string</param>
        public Database(SqlConnectionStringBuilder csBuilder)
        {
            _DatabaseName = csBuilder.InitialCatalog;
            _ServerName = csBuilder.DataSource;
            _ConnectionString = csBuilder.ConnectionString;
            scriptReader = new EmbeddedSqlScriptReader();
        }

        public string DatabaseName
        {
            get
            {
                return _DatabaseName;
            }
        }

        public string ServerName
        {
            get
            {
                return _ServerName;
            }
        }

        public string ConnectionString
        {
            get
            {
                return _ConnectionString;
            }
        }

        public override string ToString()
        {
            return _ServerName + @"\" + _DatabaseName;
        }
        //==================================================
        // Public code
        //==================================================

        /// <summary>
        /// Get the schema change history for this database
        /// </summary>
        /// <returns></returns>
        public SchemaManagement.SchemaInstall[] GetSchemaChangeHistory()
        {
            using (SqlConnection Connection = new SqlConnection(this._ConnectionString))
            {
                return SchemaManagement.Schema.GetVersionHistory(Connection, this.CommandTimeOut);
            }
        }

        /// <summary>
        /// Get the name of the sql server agent
        /// </summary>
        /// <returns></returns>
        public string GetSqlServerAgentName()
        {
            string InstanceName = this.GetInstanceName();

            if (!String.IsNullOrEmpty(InstanceName))
            {
                return String.Format("SQLAgent${0}", InstanceName);
            }
            else
            {
                return "SQLSERVERAGENT";
            }
        }

        /// <summary>
        /// Get the current state of the database
        /// </summary>
        public string State
        {
            get
            {
                string state = string.Empty;
                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(String.Format("SELECT [state_desc] FROM sys.databases WHERE [name]='{0}'", DatabaseName), connection);
                    cmd.CommandType = CommandType.Text;
                    state = cmd.ExecuteScalar().ToString();
                    connection.Close();
                }
                return state;
            }
        }

        public Version InstalledSqlVersion
        {
            get
            {
                string dbversion = string.Empty;
                using (SqlConnection connection = new SqlConnection(this.MasterDBConnectionString))
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand("SELECT SERVERPROPERTY('ProductVersion')", connection);
                    cmd.CommandType = CommandType.Text;
                    dbversion = cmd.ExecuteScalar().ToString();
                    connection.Close();
                }
                return (new Version(dbversion));
            }
        }

        public string InstalledSqlVersionString
        {
            get
            {
                string dbversion = string.Empty;
                using (SqlConnection connection = new SqlConnection(this.MasterDBConnectionString))
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand("SELECT @@VERSION", connection);
                    cmd.CommandType = CommandType.Text;
                    dbversion = cmd.ExecuteScalar().ToString();
                    connection.Close();
                }
                return dbversion;
            }
        }

        /// <summary>
        /// Get the name of the instance
        /// </summary>
        /// <returns>The instance name, or null if it is the default instance</returns>
        public string GetInstanceName()
        {
            string Query = "SELECT ISNULL( SERVERPROPERTY('INSTANCENAME'), '')";

            using (SqlConnection Connection = new SqlConnection(this._ConnectionString))
            {
                object InstanceName = Execute.ExecuteScalar(Connection, Query, CommandTimeOut);

                if (InstanceName != null)
                    return Convert.ToString(InstanceName);
            }

            return null;
        }

        /// <summary>
        /// Creates a database with the given name
        /// </summary>
        /// <param name="DatabaseName">The name of the database to create</param>
        public void CreateDatabase(string DatabaseName)
        {
            if (String.IsNullOrEmpty(DatabaseName))
                throw new ArgumentNullException("DatabaseName", "No database name specified");

            this.DropDatabase(DatabaseName);
            try
            {
                string dataFolder = LocateSQLDataFolder();
                if (!string.IsNullOrEmpty(dataFolder))
                {
                    string filePath = Path.Combine(dataFolder, DatabaseName + ".mdf");
                    if (File.Exists(filePath))
                    {
                        FireOnUpdate(DatabaseUpdateContext.Cleanup, string.Format("Deleting database data file at [{0}]", filePath));
                        File.Delete(filePath);
                    }
                    filePath = Path.Combine(dataFolder, DatabaseName + "_log.ldf");
                    if (File.Exists(filePath))
                    {
                        FireOnUpdate(DatabaseUpdateContext.Cleanup, string.Format("Deleting database log file at [{0}]", filePath));
                        File.Delete(filePath);
                    }
                }
            }
            catch (Exception ex)
            {
                FireOnUpdate(DatabaseUpdateContext.Configure,
                    String.Format("Unable to delete database files {0} - Warning: {1}", DatabaseName, ex.ToString()));
            }
            string sqlScript = string.Format(@"CREATE DATABASE [{0}] COLLATE {1}", DatabaseName, PreRequisiteCheck.RequiredCollation);
            try
            {
                this.RunScriptAgainstMaster(sqlScript);
            }
            catch (Exception e)
            {
                FireOnUpdate(DatabaseUpdateContext.Configure,
                    String.Format("Unable to create database {0} - Error: {1}", DatabaseName, e.ToString()));
            }

            //
            //  Databases should always be created with AUTO_CLOSE OFF. By default this option
            //  is turned OFF except for SQL Server Express. Since there is no way to change
            //  the default value for AUTO_CLOSE in SQL Server Express, it is necessary to 
            //  set it explicitly.
            //
            SetDatabaseOption("AUTO_CLOSE", false);
            //sqlScript = string.Format(@"ALTER DATABASE [{0}] SET AUTO_CLOSE OFF", DatabaseName);
            //this.RunScriptAgainstMaster(sqlScript);
        }

        /// <summary>
        /// Kill all the connections, and drop the database
        /// </summary>
        public void DropDatabase()
        {
            this.DropDatabase(this._DatabaseName);
        }

        /// <summary>
        /// Kill all the connections, and drop the database
        /// </summary>
        public void DropDatabase(string DatabaseName)
        {
            if (String.IsNullOrEmpty(DatabaseName))
                throw new ArgumentNullException("DatabaseName", "No database name specified");

            if (this.Exists(DatabaseName))
            {
                //
                //  Get the list of files for this database
                //  These files will be checked for deletion after
                //  the database has been dropped
                //
                List<DatabaseFile> DBFiles = null;
                try
                {
                    DBFiles = _GetDatabaseFiles();
                    foreach (DatabaseFile file in DBFiles)
                    {
                        FireOnUpdate(DatabaseUpdateContext.Information,
                            String.Format("Database file {0} will be monitored for deletion after the database is dropped", file.LogicalName));
                    }
                }
                catch (Exception e)
                {
                    FireOnUpdate(DatabaseUpdateContext.Information,
                        String.Format("Unable to retrieve the list of database files - Error: {0}", e.ToString()));
                }

                //Report the current state of the database
                try
                {
                    FireOnUpdate(DatabaseUpdateContext.Information, String.Format("Database state before DROP: {0}", State));
                }
                catch (Exception)
                {
                }
                // DROP the database
                try
                {
                    string tsqlScript = string.Format(@"ALTER DATABASE [{0}] SET ONLINE", DatabaseName);
                    try { this.RunScriptAgainstMaster(tsqlScript); }
                    catch (Exception) { }
                    tsqlScript = string.Format(@"ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE", DatabaseName);
                    this.RunScriptAgainstMaster(tsqlScript);
                }
                catch (Exception)
                {
                    SqlConnection.ClearAllPools();
                    string esqlScript = string.Format(@"ALTER DATABASE [{0}] SET OFFLINE", DatabaseName);
                    this.RunScriptAgainstMaster(esqlScript);
                }
                string sqlScript = string.Format(@"DROP DATABASE {0}", DatabaseName);
                this.KillConnections(DatabaseName);
                this.RunScriptAgainstMaster(sqlScript);

                // Checking that the files have been deleted
                FireOnUpdate(DatabaseUpdateContext.Information, "Checking if files have been correctly deleted");

                if (DBFiles != null && DBFiles.Count > 0)
                {
                    bool filesStillExist = true;
                    int retryCount = 0;
                    System.Threading.Thread.Sleep(DatabaseConstants.WAIT_TIME_BETWEEN_RETRIES_DELETE_FILES);

                    while (retryCount++ < DatabaseConstants.MAX_RETRIES_TO_DELETE_FILES && filesStillExist)
                    {
                        filesStillExist = _DatabaseFilesExist(DBFiles);
                        FireOnUpdate(DatabaseUpdateContext.Cleanup, "Database Files have not been deleted yet");
                        System.Threading.Thread.Sleep(DatabaseConstants.WAIT_TIME_BETWEEN_RETRIES_DELETE_FILES);
                    }

                    if (filesStillExist)
                    {
                        throw new Exception("The database was dropped but at least one file has not been deleted");
                    }
                    else
                    {
                        FireOnUpdate(DatabaseUpdateContext.Cleanup, "Database Files were deleted successfully");
                    }
                }
            }
        }

        /// <summary>
        /// Get both the Windows server and instance information 
        /// (This method should be renamed)
        /// </summary>
        /// <returns>The server name</returns>
        public string GetNetBiosServerName()
        {
            string commandText = "SELECT SERVERPROPERTY('ServerName')";

            using (SqlConnection Connection = new SqlConnection(this.MasterDBConnectionString))
            {
                object NameObject = Execute.ExecuteScalar(Connection, commandText, this.CommandTimeOut);

                if (NameObject != null)
                    return NameObject.ToString();
            }

            return null;
        }

        /// <summary>
        /// Get collation for the server 
        /// </summary>
        /// <returns>Collation string</returns>
        public string GetServerCollation()
        {
            string commandText = "SELECT SERVERPROPERTY('Collation')";

            using (SqlConnection Connection = new SqlConnection(this._ConnectionString))
            {
                object NameObject = Execute.ExecuteScalar(Connection, commandText, this.CommandTimeOut);

                if (NameObject != null)
                    return NameObject.ToString();
            }

            return null;
        }

        /// <summary>
        /// Get collation for the database 
        /// </summary>
        /// <returns>Collation string</returns>
        public string GetDatabaseCollation()
        {
            string commandText = "SELECT collation_name FROM sys.databases WHERE name = '" + this._DatabaseName + "'";
            using (SqlConnection Connection = new SqlConnection(this._ConnectionString))
            {
                object NameObject = Execute.ExecuteScalar(Connection, commandText, this.CommandTimeOut);

                if (NameObject != null)
                    return NameObject.ToString();
            }

            return null;
        }

        /// <summary>
        /// Get the net bios name of the database server
        /// </summary>
        /// <returns>The server name</returns>
        public string GetComputerNamePhysicalNetBIOS()
        {
            string commandText = "SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')";

            using (SqlConnection Connection = new SqlConnection(this.MasterDBConnectionString))
            {
                object NameObject = Execute.ExecuteScalar(Connection, commandText, this.CommandTimeOut);

                if (NameObject != null)
                    return NameObject.ToString();
            }

            return null;
        }

        public bool Exists()
        {
            return Exists(this._DatabaseName);
        }

        /// <summary>
        /// Checks if the given databsae exists
        /// </summary>
        /// <param name="DatabaseName">The name of the database to check for</param>
        /// <returns></returns>
        public bool Exists(string DatabaseName)
        {
            if (String.IsNullOrEmpty(DatabaseName))
                throw new ArgumentNullException("DatabaseName", "No database name specified");

            bool exists = false;

            using (SqlConnection connection = new SqlConnection(this.MasterDBConnectionString))
            {
                string QueryString = String.Format(@"select count(*) from master.dbo.sysdatabases where [name]='{0}'", DatabaseName);
                object CountObject = Execute.ExecuteScalar(connection, QueryString, CommandTimeOut);

                // Make sure the return objet isn't null
                // and check to see if it is 1
                if (CountObject != null
                    && ((int)CountObject) == 1)
                {
                    exists = true;
                }
            }

            return exists;
        }

        /// <summary>
        /// Opens and closes a connection to the database to test connectivity to the server.  Will throw
        /// the standard Sql Exceptions if a connection can not be created
        /// </summary>
        public void Ping()
        {
            using (SqlConnection Connection = new SqlConnection(this.MasterDBConnectionString))
            {
                Connection.Open();
            }
        }

        /// <summary>
        /// Pings the linked server via the source server to ensure it is properly setup
        /// </summary>
        /// <param name="linkedServerName">The net bios name of the linked server to ping</param>
        /// <returns>True if the server can be pinged, false otherwise</returns>
        public bool PingLinkedServer(String linkedServerNetBiosName)
        {
            if (linkedServerNetBiosName == null) throw new ArgumentNullException("linkedServerNetBiosName", "An invalid linked server was specified");
            if (linkedServerNetBiosName == String.Empty) throw new ArgumentException("An empty linked server was specified", "linkedServerNetBiosName");

            SqlCommand cmd = scriptReader.GetScriptCommand("UDS.Management.Data.Sql.SetupAndValidateLinkedServer.sql", new SqlParameter[] { new SqlParameter("@linkedServerName", linkedServerNetBiosName) });
            using (SqlConnection connection = new SqlConnection(this._ConnectionString))
            {
                connection.Open();
                cmd.Connection = connection;
                cmd.CommandTimeout = CommandTimeOut;
                object str = cmd.ExecuteScalar();

                if (str != null)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        /// <summary>
        /// Kills the connections to the database (overload with name).
        /// </summary>
        public string LocateSQLDataFolder()
        {
            try
            {
                SqlCommand cmd = scriptReader.GetScriptCommand("UDS.Management.Data.Sql.LocateSQLDataFolder.sql");
                using (SqlConnection connection = new SqlConnection(this.MasterDBConnectionString))
                {
                    connection.Open();
                    cmd.Connection = connection;
                    cmd.CommandTimeout = CommandTimeOut;
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return reader["MasterDBPath"].ToString();
                        }
                    }
                }
            }
            catch (SqlException)
            {
            }
            return string.Empty;
        }

        /// <summary>
        /// Kills the connections to the database (overload with name).
        /// </summary>
        public void KillConnections(string DatabaseName)
        {
            if (String.IsNullOrEmpty(DatabaseName))
                throw new ArgumentNullException("DatabaseName", "No database name specified");

            bool done = false;
            int retries = 0;

            do
            {
                try
                {
                    SqlCommand cmd = scriptReader.GetScriptCommand("UDS.Management.Data.Sql.KillConnections.sql", new SqlParameter[] { new SqlParameter("@databaseName", DatabaseName) });
                    using (SqlConnection connection = new SqlConnection(this.MasterDBConnectionString))
                    {
                        connection.Open();
                        cmd.Connection = connection;
                        cmd.CommandTimeout = CommandTimeOut;
                        cmd.ExecuteNonQuery();
                    }
                    done = true;
                }
                catch (SqlException)
                {
                    retries++;
                    System.Threading.Thread.Sleep(100);
                }
            }
            while (!done && retries < MAX_RETRIES);
            SqlConnection.ClearAllPools();
        }


        public void RunScript(string ScriptText)
        {
            using (SqlConnection Connection = new SqlConnection(this._ConnectionString))
            {
                Connection.Open();

                using (SqlSource src = new SqlSource("ScriptContent", new StringReader(ScriptText)))
                {
                    SqlHelper.ExecuteContent(src, Connection, CommandTimeOut);
                }
            }
        }

        /// <summary>
        /// Execute the scripts specified against the database
        /// </summary>
        /// <param name="ScriptLocations">The script to execute</param>
        public void RunScripts(params string[] ScriptLocations)
        {
            using (SqlConnection Connection = new SqlConnection(this._ConnectionString))
            {
                Connection.Open();

                foreach (string ScriptLocation in ScriptLocations)
                {
                    using (Package.SqlSource src = new SqlSource(ScriptLocation, new StreamReader(ScriptLocation)))
                    {
                        SqlHelper.ExecuteContent(src, Connection, this.CommandTimeOut);
                    }
                }
            }
        }


        /// <summary>
        /// Execute the specified stored procedure
        /// </summary>
        /// <param name="Name">The name of the stored procedure to execute</param>
        /// <param name="Args">The optional parameters</param>
        public void ExecuteStoredProcedure(string Name, params SqlParameter[] Args)
        {
            using (SqlConnection Connection = new SqlConnection(this._ConnectionString))
            {
                Connection.Open();
                if (Args != null && Args.Length > 0)
                {
                    Execute.ExecuteStoredProcedure(Connection, Name, this.CommandTimeOut, Args);
                }
                else
                {
                    Execute.ExecuteStoredProcedure(Connection, Name, this.CommandTimeOut);
                }
            }
        }

        public void DeleteServiceGroup(Guid serviceGroupId)
        {
            string SprocName = "usp_delete_servicegroup";

            using (SqlConnection Connection = new SqlConnection(this._ConnectionString))
            {
                using (SqlTransaction Transaction = Connection.BeginTransaction())
                {
                    SqlParameter[] Params = new SqlParameter[1];
                    Params[0] = new SqlParameter("serviceGroupId", serviceGroupId);
                    Execute.ExecuteStoredProcedure(Transaction, SprocName, this.CommandTimeOut, Params);
                }
            }
        }

        public SlaveDB[] GetServiceGroups()
        {
            List<SlaveDB> sgList = new List<SlaveDB>();

            string cmd = "usp_select_all_servicegroups_V_2";
            using (SqlConnection Connection = new SqlConnection(this._ConnectionString))
            {
                using (SqlDataReader reader = Execute.ExecuteReader(Connection, cmd, this.CommandTimeOut))
                {
                    while (reader.Read())
                    {
                        SlaveDB sg = new SlaveDB();
                        sg.Id = new Guid(reader["serviceGroupId"].ToString());
                        sg.Name = reader["name"].ToString();
                        sg.Description = reader["description"].ToString();
                        sgList.Add(sg);
                    }
                }
            }
            return sgList.ToArray();
        }

        public string GetServiceGroupVersion()
        {
            string Version = null;

            using (SqlConnection Connection = new SqlConnection(this._ConnectionString))
            {
                Connection.Open();

                string CommandText = "if ( object_id('dbo.DatabaseVersion') is not null )  begin Select Id, ReplSchemaVersion, SchemaVersion from DatabaseVersion end else begin select newid() as Id, '1.1' as ReplSchemaVersion, '1.1' as SchemaVersion end ";

                using (SqlDataReader reader = Execute.ExecuteReader(Connection, CommandText, this.CommandTimeOut))
                {
                    while (reader.Read())
                    {
                        Version = reader["ReplSchemaVersion"].ToString();
                    }
                }
            }
            return Version;
        }

        public int GetNumberOfServiceGroups(Guid serviceGroupId)
        {
            string cmd = string.Format("select count(*) from bm_account where servicegroupId = '{0}'", serviceGroupId.ToString());
            using (SqlConnection Connection = new SqlConnection())
            {
                Connection.Open();
                int count = (int)Execute.ExecuteScalar(Connection, cmd, this.CommandTimeOut);
                return count;
            }
        }

        /// <summary>
        /// Sets the specified option to on or off
        /// </summary>
        /// <param name="OptionName">The name of the option to set</param>
        /// <param name="OptionValue">Sets the state of the option</param>
        public void SetDatabaseOption(string optionName, bool optionValue)
        {
            string sql = String.Format("ALTER DATABASE {0} SET {1} {2}", this._DatabaseName, optionName, optionValue ? "ON" : "OFF");
            this.RunScriptAgainstMaster(sql);
        }
        /// <summary>
        /// Install replication on this database
        /// </summary>
        public void InstallReplication(string SnapshotDirectory)
        {
            using (SqlConnection Connection = new SqlConnection(_ConnectionString))
            {
                Connection.Open();
                InstallReplication(Connection, SnapshotDirectory);
            }
        }

        /// <summary>
        /// Uninstall replication from this database
        /// </summary>
        public void UninstallReplication()
        {
            using (SqlConnection Connection = new SqlConnection(_ConnectionString))
            {
                Connection.Open();
                UninstallReplication(Connection);
            }
        }

        /// <summary>
        /// Publish the replication
        /// </summary>
        public void PublishReplication()
        {
            using (SqlConnection Connection = new SqlConnection(_ConnectionString))
            {
                Connection.Open();
                PublishReplication(Connection);
            }
        }

        /// <summary>
        /// recover sql jobs for a specific database
        /// </summary>
        public void RecoverSQLJobs()
        {
            using (SqlConnection Connection = new SqlConnection(_ConnectionString))
            {
                Connection.Open();
                RecoverSQLJobs(Connection);
            }
        }

        /// <summary>
        /// remove sql jobs for a specific database
        /// </summary>
        public void RemoveSQLJobs()
        {
            using (SqlConnection Connection = new SqlConnection(_ConnectionString))
            {
                Connection.Open();
                RemoveSQLJobs(Connection);
            }
        }
        /// <summary>
        /// Unpublish the replication
        /// </summary>
        public void UnpublishReplication()
        {
            using (SqlConnection Connection = new SqlConnection(_ConnectionString))
            {
                Connection.Open();
                UnpublishReplication(Connection);
            }
        }

        /// <summary>
        /// Subscribe the replication to the specified database server name and database name
        /// </summary>
        /// <param name="DatabaseServerName">The name of the subscription database server</param>
        /// <param name="DatabaseName">The name of the subscription database</param>
        public void SubscribeReplication(string DatabaseServerName)
        {
            using (SqlConnection Connection = new SqlConnection(_ConnectionString))
            {
                Connection.Open();
                SubscribeReplication(DatabaseServerName, Connection);
            }
        }

        /// <summary>
        /// ReSubscribe all service groups 
        /// </summary>
        public void ReSubscribeAll()
        {
            using (SqlConnection Connection = new SqlConnection(_ConnectionString))
            {
                Connection.Open();
                ReSubscribeAll(Connection);
            }
        }

        public void RegisterSlaveDB(string slaveName, string slaveServerNetBiosName, string slaveVersion)
        {
            using (SqlConnection Connection = new SqlConnection(_ConnectionString))
            {
                Connection.Open();
                RegisterSlaveDB(slaveName, slaveServerNetBiosName, slaveVersion, Connection);
            }
        }

        public SlaveDBRegistration GetSlaveDBRegistration(string slaveName)
        {
            using (SqlConnection Connection = new SqlConnection(_ConnectionString))
            {
                Connection.Open();
                return GetSlaveDBRegistration(slaveName, Connection);
            }
        }

        public void SetSlaveVersion(SlaveDBRegistration slaveRegistration, string slaveVersion)
        {
            using (SqlConnection Connection = new SqlConnection(_ConnectionString))
            {
                Connection.Open();
                SetSlaveVersion(slaveRegistration, slaveVersion, Connection);
            }
        }

        /// <summary>
        /// Unsubscribe replication from the specified database server name 
        /// </summary>
        /// <param name="DatabaseServerName">The name of the subscription database server</param>
        public void UnsubscribeReplication(string DatabaseServerName)
        {
            using (SqlConnection Connection = new SqlConnection(_ConnectionString))
            {
                Connection.Open();
                UnsubscribeReplication(DatabaseServerName, Connection);
            }
        }

        public void WriteContent(Package.IDBPackage databasePackage, DatabaseUpdateContext schemaUpdateFlags, System.IO.TextWriter writer)
        {
            if (databasePackage == null)
            {
                throw new ArgumentNullException("databasePackage", "An invalid database package was specified");
            }

            if ((schemaUpdateFlags & DatabaseUpdateContext.Configure) == DatabaseUpdateContext.Configure)
            {
                IEnumerable<SqlSource> ConfigurationEnumerable = databasePackage.GetConfigurationScripts();

                if (ConfigurationEnumerable != null)
                {
                    WriteSourceContent(ConfigurationEnumerable, writer);
                }
            }

            if ((schemaUpdateFlags & DatabaseUpdateContext.PreInstall) == DatabaseUpdateContext.PreInstall)
            {
                IEnumerable<SqlSource> PreStepEnumerable = databasePackage.GetPreSteps();

                if (PreStepEnumerable != null)
                {
                    WriteSourceContent(PreStepEnumerable, writer);
                }
            }

            if ((schemaUpdateFlags & DatabaseUpdateContext.Schema) == DatabaseUpdateContext.Schema)
            {
                IEnumerable<SqlSchemaChangeFamily> SchemaEnumerable = databasePackage.GetSchemaChanges();

                if (SchemaEnumerable != null)
                {
                    WriteSchemaContent(SchemaEnumerable, writer);
                }
            }

            if ((schemaUpdateFlags & DatabaseUpdateContext.StoredProcedures) == DatabaseUpdateContext.StoredProcedures)
            {
                IEnumerable<SqlSource> StoredProcedureEnumerable = databasePackage.GetStoredProcedures();

                if (StoredProcedureEnumerable != null)
                {
                    WriteSourceContent(StoredProcedureEnumerable, writer);
                }
            }

            if ((schemaUpdateFlags & DatabaseUpdateContext.Views) == DatabaseUpdateContext.Views)
            {
                IEnumerable<SqlSource> ViewEnumerable = databasePackage.GetViews();

                if (ViewEnumerable != null)
                {
                    WriteSourceContent(ViewEnumerable, writer);
                }
            }

            if ((schemaUpdateFlags & DatabaseUpdateContext.Functions) == DatabaseUpdateContext.Functions)
            {
                IEnumerable<SqlSource> FunctionEnumerable = databasePackage.GetFunctions();

                if (FunctionEnumerable != null)
                {
                    WriteSourceContent(FunctionEnumerable, writer);
                }
            }

            if ((schemaUpdateFlags & DatabaseUpdateContext.Jobs) == DatabaseUpdateContext.Jobs)
            {
                // Install the jobs
                IEnumerable<SqlSource> Jobs = databasePackage.GetJobs();

                if (Jobs != null)
                {
                    WriteSourceContent(Jobs, writer);
                }
            }

            if ((schemaUpdateFlags & DatabaseUpdateContext.Replication) == DatabaseUpdateContext.Replication)
            {
                // Install replication scripts
                IEnumerable<SqlSource> Replication = databasePackage.GetReplicationScripts();

                if (Replication != null)
                {
                    WriteSourceContent(Replication, writer);
                }
            }
            if ((schemaUpdateFlags & DatabaseUpdateContext.PostInstall) == DatabaseUpdateContext.PostInstall)
            {
                IEnumerable<SqlSource> PostStepEnumerable = databasePackage.GetPostSteps();

                if (PostStepEnumerable != null)
                {
                    WriteSourceContent(PostStepEnumerable, writer);
                }
            }
        }

        public void WriteContent(Package.IDBPackage databasePackage, System.IO.TextWriter writer)
        {
            // Update the database with all of the schema elements
            WriteContent(databasePackage,
                DatabaseUpdateContext.Configure
                | DatabaseUpdateContext.Functions
                | DatabaseUpdateContext.Jobs
                | DatabaseUpdateContext.PostInstall
                | DatabaseUpdateContext.PreInstall
                | DatabaseUpdateContext.Publish
                | DatabaseUpdateContext.Replication
                | DatabaseUpdateContext.Schema
                | DatabaseUpdateContext.StoredProcedures
                | DatabaseUpdateContext.Unpublish
                | DatabaseUpdateContext.Views,
                writer);
        }

        /// <summary>
        /// Execute the database package against the database
        /// </summary>
        /// <exception cref="Exceptions.SqlScriptException">Thrown if there is an sql script error</exception>
        /// <exception cref="Execptions.DatabaseManagementException">Thrown if the version of the package is greater than the version of the library</exception>
        /// <exception cref="System.ArgumentNullException">Thrown if no database package is specified</exception>
        /// <param name="databasePackage">The database package to execute</param>        
        public void UpdateDatabase(Package.IDBPackage databasePackage, bool allowReinstall)
        {
            // Update the database with all of the schema elements
            UpdateDatabase(databasePackage,
                DatabaseUpdateContext.Configure
                | DatabaseUpdateContext.Functions
                | DatabaseUpdateContext.Jobs
                | DatabaseUpdateContext.PostInstall
                | DatabaseUpdateContext.PreInstall
                | DatabaseUpdateContext.Publish
                | DatabaseUpdateContext.Replication
                | DatabaseUpdateContext.Schema
                | DatabaseUpdateContext.StoredProcedures
                | DatabaseUpdateContext.Unpublish
                | DatabaseUpdateContext.Cleanup
                | DatabaseUpdateContext.Views,
                allowReinstall);
        }

        private bool CheckPackageInstall(SqlConnection connection, Guid packageId)
        {
            SqlCommand cmd = new SqlCommand(string.Format("SELECT COUNT(*) FROM XBSchemaVersion.SchemaInstall WHERE packageId = '{0}' AND Status = 'Success' AND installType = 'Update'", packageId), connection);
            object result = cmd.ExecuteScalar();
            if (result != null)
            {
                if ((int)result > 0)
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// Execute the database package against the database
        /// </summary>
        /// <exception cref="Exceptions.SqlScriptException">Thrown if there is an sql script error</exception>
        /// <exception cref="Execptions.DatabaseManagementException">Thrown if the version of the package is greater than the version of the library</exception>
        /// <exception cref="System.ArgumentNullException">Thrown if no database package is specified</exception>
        /// <param name="databasePackage">The database package to execute</param>
        /// <param name="schemaUpdateFlags">Only run the specified schema elements</param>
        public void UpdateDatabase(Package.IDBPackage databasePackage, DatabaseUpdateContext schemaUpdateFlags, bool allowReinstall)
        {
            if (databasePackage == null)
            {
                throw new ArgumentNullException("databasePackage", "An invalid database package was specified");
            }

            // Validate that the manifest version and the dll are compatible
            CheckVersion(databasePackage.GetPackageVersion(), Schema.MANIFEST_VERSION);

            // Create an install object 
            SchemaInstall Install = CreateInstall(databasePackage, schemaUpdateFlags);

            bool AppliedChange = false;

            // Create the database management schema and ensure the current version is not
            // greater than the library version.
            using (SqlConnection Connection = new SqlConnection(this._ConnectionString))
            {
                Connection.Open();
                // Also - need to make sure the DatabaseVersion table exists
                // This will be a no-op if the table already exists
                Schema.CreateDatabaseVersionSchema(Connection, this.CommandTimeOut);

                // Check if package has already been installed
                if (!allowReinstall && schemaUpdateFlags != DatabaseUpdateContext.Cleanup)
                {
                    if (CheckPackageInstall(Connection, Install.PackageId))
                    {
                        throw new UDS.Management.Data.Exceptions.PackageAlreadyInstalledException(Install);
                    }
                }

                using (SqlTransaction SchemaInstallTran = Connection.BeginTransaction())
                {
                    // Apply the schema install
                    try
                    {
                        Schema.ApplySchemaInstall(SchemaInstallTran, Install, this.CommandTimeOut);
                        SchemaInstallTran.Commit();
                    }
                    catch
                    {
                        try
                        {
                            SchemaInstallTran.Rollback();
                        }
                        catch { }
                        throw;
                    }
                }
            }

            try
            {
                if ((schemaUpdateFlags & DatabaseUpdateContext.Configure) == DatabaseUpdateContext.Configure)
                {
                    #region DB configurations
                    IEnumerable<SqlSource> ConfigurationEnumerable = databasePackage.GetConfigurationScripts();

                    if (ConfigurationEnumerable != null)
                    {
                        // These scripts can't be executed on a transaction, or they will fail,
                        // so just execute them on a connection.
                        using (SqlConnection Connection = new SqlConnection(this._ConnectionString))
                        {
                            Connection.Open();

                            foreach (SqlSource Source in ConfigurationEnumerable)
                            {
                                using (Source)
                                {
                                    FireOnUpdate(DatabaseUpdateContext.Configure, Source.Name);
                                    SqlHelper.ExecuteContent(Source, Connection, this.CommandTimeOut);
                                }
                            }
                        }
                    }
                    #endregion
                }

                using (SqlConnection Connection = new SqlConnection(this._ConnectionString))
                {
                    Connection.Open();

                    // Get the current time of the database
                    DateTime currentDbTime = SqlHelper.GetCurrentDBTime(Connection);

                    if ((schemaUpdateFlags & DatabaseUpdateContext.PreInstall) == DatabaseUpdateContext.PreInstall)
                    {
                        #region DB pre-install
                        IEnumerable<SqlSource> PreStepEnumerable = databasePackage.GetPreSteps();

                        if (PreStepEnumerable != null)
                        {
                            // Run any pre-steps
                            UpdateSources(DatabaseUpdateContext.PreInstall, PreStepEnumerable, Connection);
                        }
                        #endregion
                    }

                    // create the SQL jobs tables
                    Schema.CreateSQLJobsMetaDataSchema(Connection, CommandTimeOut);

                    // create tables for replication
                    if (databasePackage.Manifest.Replication != null)
                    {
                        if ((schemaUpdateFlags & DatabaseUpdateContext.Replication) == DatabaseUpdateContext.Replication)
                        {
                            Schema.CreateReplicationMetaDataSchema(Connection, CommandTimeOut);
                        }
                    }

                    if ((schemaUpdateFlags & DatabaseUpdateContext.Schema) == DatabaseUpdateContext.Schema)
                    {
                        #region DB Schema changes
                        IEnumerable<SqlSchemaChangeFamily> SchemaEnumerable = databasePackage.GetSchemaChanges();

                        if (SchemaEnumerable != null)
                        {
                            // Now update the schema
                            AppliedChange = UpdateSchema(SchemaEnumerable, Install.InstallId, Connection);
                        }
                        #endregion
                    }

                    if ((schemaUpdateFlags & DatabaseUpdateContext.Functions) == DatabaseUpdateContext.Functions)
                    {
                        #region DB Functions
                        IEnumerable<SqlSource> FunctionEnumerable = databasePackage.GetFunctions();

                        if (FunctionEnumerable != null)
                        {
                            // Run the functions
                            UpdateSources(DatabaseUpdateContext.Functions, FunctionEnumerable, Connection);
                        }
                        #endregion
                    }

                    if ((schemaUpdateFlags & DatabaseUpdateContext.Views) == DatabaseUpdateContext.Views)
                    {
                        #region DB Views
                        IEnumerable<SqlSource> ViewEnumerable = databasePackage.GetViews();

                        if (ViewEnumerable != null)
                        {
                            // Run the views
                            UpdateSources(DatabaseUpdateContext.Views, ViewEnumerable, Connection);
                        }
                        #endregion
                    }

                    if ((schemaUpdateFlags & DatabaseUpdateContext.StoredProcedures) == DatabaseUpdateContext.StoredProcedures)
                    {
                        #region DB sprocs
                        SortedDictionary<string, StoredProcedureDefinition> StoredProcedureEnumerable = databasePackage.GetStoredProceduresWithGrants();

                        if (StoredProcedureEnumerable != null)
                        {
                            // Run the stored procedures
                            UpdateStoredProcedures(StoredProcedureEnumerable, Connection);
                        }
                        #endregion
                    }

                    if ((schemaUpdateFlags & DatabaseUpdateContext.Jobs) == DatabaseUpdateContext.Jobs)
                    {
                        #region DB jobs
                        // Build Recovery Jobs if never been built
                        DBDataJobs.FillDBIfEmpty(Connection.Database, Connection);
                        // Install the jobs
                        IEnumerable<SqlSource> Jobs = databasePackage.GetJobs();

                        if (Jobs != null)
                        {
                            UpdateSources(DatabaseUpdateContext.Jobs, Jobs, Connection);
                        }


                        // save the patched jobs in the database
                        IEnumerable<SqlSource> sqlJobs = databasePackage.GetXMLJobs();
                        this.FireOnUpdate(DatabaseUpdateContext.Jobs, "Loading SQL Jobs definition data from manifest");
                        SortedDictionary<string, SQLJobDefinition> jobsData = databasePackage.GetManifestSQLJobData();
                        ///if ( jobsData.Count != sqlJobs.Length ) fails
                        foreach (SqlSource src in Jobs)
                        {
                            string fileLocation = src.Name;
                            string fileName = GetSqlSourceJobName(src.Name);

                            SQLJobDefinition thejob = null;
                            jobsData.TryGetValue(fileName, out thejob);
                            if (thejob == null)
                            {
                                throw new Exception("The SQL Job does not have a related XML file that define it.");
                            }
                            DBDataJobs.LoadDBJobsData(thejob, Connection);
                        }
                        #endregion
                    }

                    // only happens at build time
                    if ((schemaUpdateFlags & DatabaseUpdateContext.GenerateSQLJobs) == DatabaseUpdateContext.GenerateSQLJobs)
                    {
                        #region Generate jobs
                        IEnumerable<SqlSource> Jobs = databasePackage.GetJobs();

                        if (Jobs != null)
                        {
                            FireOnUpdate(DatabaseUpdateContext.Jobs, "Getting SQL Jobs XML Definitions from manifest");
                            IEnumerable<SqlSource> sqlJobs = databasePackage.GetXMLJobs();
                            FireOnUpdate(DatabaseUpdateContext.Jobs, "Loading SQL Jobs definition data from manifest");
                            SortedDictionary<string, SQLJobDefinition> jobsData = databasePackage.GetManifestSQLJobData();

                            foreach (SqlSource src in Jobs)
                            {
                                src.Content.ResetContentCounters();
                                string fileLocation = src.Name;
                                string fileName = GetSqlSourceJobName(src.Name);
                                SQLJobDefinition xmlJob = jobsData[fileName];
                                if (xmlJob == null)
                                {
                                    throw new Exception(string.Format("SQL Job {0} is not defined in the manifest as an XML file", src.Name));
                                }

                                string jobStr = string.Empty;
                                StringBuilder jobFileBuilder = new StringBuilder();
                                while (jobStr != null)
                                {
                                    if (jobStr != string.Empty)
                                    {
                                        jobFileBuilder.Append(jobStr);
                                        jobFileBuilder.Append("go\r\n");
                                    }
                                    jobStr = src.Content.ReadCommand();
                                }
                                src.Content.ResetContentCounters();
                                jobStr = jobFileBuilder.ToString();

                                string trimmedJobStr = jobStr.Replace(" ", "").Replace("\r", "").Replace("\n", "").Replace("\t", "").Trim();

                                SQLJobGenerator xmlJobGenerator = new SQLJobGenerator(xmlJob);
                                string xmlJobStr = xmlJobGenerator.GenerateSQL();
                                string trimmedXMLJobStr = xmlJobStr.Replace(" ", "").Replace("\r", "").Replace("\n", "").Replace("\t", "").Trim() + "go";
                                if (string.Compare(trimmedJobStr, trimmedXMLJobStr, true) != 0)
                                {
                                    try
                                    {
                                        //special case for backenddb and slavedb
                                        string roleName = databasePackage.GetDatabaseName();
                                        const string backendSlave = "WowoAdDB";
                                        string fPath = string.Empty;
                                        if ((string.Compare(roleName, "BackendDB", true) == 0))
                                        {
                                            fPath = Environment.ExpandEnvironmentVariables(src.Name.Replace("UDS.BackendDB", @"%XB_ROOT%\Development\Database\" + backendSlave).Replace(".sql", ":sql").Replace(".", @"\").Replace(":", "."));
                                        }
                                        else if ((string.Compare(roleName, "SlaveDB", true) == 0))
                                        {
                                            fPath = Environment.ExpandEnvironmentVariables(src.Name.Replace("UDS.SlaveDB", @"%XB_ROOT%\Development\Database\" + backendSlave).Replace(".sql", ":sql").Replace(".", @"\").Replace(":", "."));
                                        }
                                        else
                                        {
                                            fPath = Environment.ExpandEnvironmentVariables(src.Name.Replace("UDS.Database", @"%XB_ROOT%\Development\Database\").Replace(".sql", ":sql").Replace(".", @"\").Replace(":", "."));
                                        }

                                        File.WriteAllText(fPath, xmlJobStr);
                                        this.FireOnUpdate(DatabaseUpdateContext.Jobs, String.Format("Updated the file [{0}] with new generated SQL content, please rebuild", fPath));
                                    }
                                    catch (Exception ex)
                                    {
                                        throw new Exception(string.Format("You need to edit the SQL File for the SQL Job [{0}] for the file [{1}] before check-in. Execption {2}.", src.Name, src.Name, ex.Message));
                                    }
                                }
                            }
                        }
                        #endregion
                    }

                    if (databasePackage.Manifest.Replication != null)
                    {
                        if ((schemaUpdateFlags & DatabaseUpdateContext.Replication) == DatabaseUpdateContext.Replication)
                        {
                            this.FireOnUpdate(DatabaseUpdateContext.Replication, "Getting replication scripts");
                            // Install replication scripts
                            IEnumerable<SqlSource> Replication = databasePackage.GetReplicationScripts();

                            this.FireOnUpdate(DatabaseUpdateContext.Replication, "Loading database replication definition data");
                            ReplXMLHelper replXMLHelper = new ReplXMLHelper();
                            foreach (ScriptSource source in databasePackage.GetReplicationDefinitions())
                            {
                                Publication publication = replXMLHelper.Load(source);
                                this.FireOnUpdate(DatabaseUpdateContext.Replication, String.Format("Loading publication {0} {1}", publication.Name, publication.Version));
                                DBDataReplication.LoadReplicationData(publication, Connection);
                            }
                            if (Replication != null)
                            {
                                UpdateSources(DatabaseUpdateContext.Replication, Replication, Connection);
                            }
                        }
                    }

                    if ((schemaUpdateFlags & DatabaseUpdateContext.PostInstall) == DatabaseUpdateContext.PostInstall)
                    {
                        IEnumerable<SqlSource> PostStepEnumerable = databasePackage.GetPostSteps();

                        if (PostStepEnumerable != null)
                        {
                            // Lastly, any post steps
                            UpdateSources(DatabaseUpdateContext.PostInstall, PostStepEnumerable, Connection);
                        }
                    }

                    if ((schemaUpdateFlags & DatabaseUpdateContext.Cleanup) == DatabaseUpdateContext.Cleanup)
                    {
                        #region DB cleanup
                        IEnumerable<SqlSchemaChangeFamily> SchemaEnumerable = databasePackage.GetSchemaChanges();

                        if (SchemaEnumerable != null)
                        {
                            // Now cleanup the schema
                            CleanupSchema(SchemaEnumerable, Install.InstallId, Connection);
                        }

                        // TODO:
                        // Maybe a bug here? Need further investigation.
#if false
                        // Drop legacy stored procedures
                        using (SqlTransaction trans = Connection.BeginTransaction())
                        {
                            try
                            {
                                string[] legacyStoredProcedures = SqlHelper.GetStoredProcedures(trans, currentDbTime);
                                foreach (string sp in legacyStoredProcedures)
                                {
                                    FireOnUpdate(DatabaseUpdateContext.Cleanup, sp);
                                    SqlHelper.DropStoredProcedure(trans, sp, CommandTimeOut);
                                }

                                trans.Commit();
                            }
                            catch
                            {
                                try
                                {
                                    trans.Rollback();
                                }
                                catch { }
                                throw;
                            }
                        }
#endif
                        #endregion
                    }
                }

                // If no changes were applied, mark the status correctly
                if (!AppliedChange)
                {
                    Install.Status = InstallStatus.AlreadyInstalled;
                    FireOnUpdate(DatabaseUpdateContext.Information, String.Format("All changes for package '{0}' have already been applied", Install.PackageName));
                }
                else
                {
                    Install.Status = InstallStatus.Success;
                    FireOnUpdate(DatabaseUpdateContext.Information, String.Format("Package '{0}' applied successfully", Install.PackageName));
                }
            }
            catch (Exception ex)
            {
                // If there was an exception applying the schema change, log it, and set the status to error
                Install.Status = InstallStatus.Failed;
                Install.StatusText = ex.Message;
                FireOnUpdate(DatabaseUpdateContext.Information, String.Format("Package '{0}' could not be applied.", Install.PackageName));
                throw;
            }
            finally
            {
                // Always update the schema install status
                using (SqlConnection Connection = new SqlConnection(this._ConnectionString))
                {
                    Connection.Open();
                    using (SqlTransaction versionTran = Connection.BeginTransaction())
                    {
                        try
                        {
                            Schema.UpdateSchemaInstallStatus(versionTran, Install, this.CommandTimeOut);
                            versionTran.Commit();
                        }
                        catch
                        {
                            try
                            {
                                versionTran.Rollback();
                            }
                            catch { }

                            throw;
                        }
                    }
                }
            }
        }

        //=======================================
        // Private implementation code
        //=======================================

        private string GetSqlSourceJobName(string srcName)
        {
            string[] split = srcName.Split('.');
            return split[split.Length - 2];
        }


        /// <summary>
        /// The version of the manifest must match the library manifest version
        /// </summary>
        /// <exception cref="Exceptions.DatabaseManagementException">Thrown if the package version doesn't match the library version.</exception>
        /// <param name="packageVersion">The package version</param>
        /// <param name="libraryVersion">The version of the database library</param>
        private void CheckVersion(string manifestVersion, string libraryVersion)
        {
            if (String.IsNullOrEmpty(manifestVersion))
            {
                throw new ArgumentNullException("manifestVersion", "An invalid manifest version was specified");
            }

            if (String.IsNullOrEmpty(libraryVersion))
            {
                throw new ArgumentNullException("libraryVersion", "An invalid library version was specified");
            }

            System.Version LibraryVersion = new System.Version(libraryVersion);
            System.Version ManifestPackageVersion = new System.Version(manifestVersion);

            if (LibraryVersion != ManifestPackageVersion)
            {
                throw new Exceptions.DatabaseManagementException(
                        String.Format("Database package version {0} does not match the library version {1}.  Please update the database management library to a compatible version",
                        ManifestPackageVersion.ToString(),
                        LibraryVersion.ToString())
                    );
            }
        }

        private SchemaInstall CreateInstall(IDBPackage package, DatabaseUpdateContext updateFlags)
        {
            SchemaInstall Install = new SchemaInstall();
            Install.InstalledBy = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
            Install.PackageId = package.GetDatabaseId();
            Install.PackageName = package.GetPackageName();
            Install.PackageDescription = package.GetPackageDescription();
            Install.InstallId = Guid.NewGuid();
            Install.Status = InstallStatus.Processing;
            if (updateFlags == DatabaseUpdateContext.Cleanup)
            {
                Install.installType = InstallType.Cleanup;
            }
            else
            {
                Install.installType = InstallType.Update;
            }
            return Install;
        }

        /// <summary>
        /// Updates the schema portion of the database package.  Also
        /// responsible for updating the version information.  If no changes
        /// are applied, false is returned.
        /// </summary>
        /// <exception cref="Exceptions.SqlScriptException">Thrown if there is an sql script error</exception>
        /// <param name="schemaEnumerable">The list of schema changes to apply</param>
        /// <param name="installId">The id of this installation</param>
        /// <param name="connection">The connection to use to update the database</param>
        /// <returns>True if at least one of the schema changes is applied, false if all changes have already been applied</returns>
        private bool UpdateSchema(IEnumerable<SqlSchemaChangeFamily> schemaEnumerable, Guid installId, SqlConnection connection)
        {
            bool ApplyingChange = false;

            // First get all of the current changes
            Dictionary<Guid, SchemaManagement.SchemaChange> CurrentChanges = Schema.GetAppliedChanges(connection, this.CommandTimeOut);

            bool NoChanges = true;

            foreach (SqlSchemaChangeFamily Family in schemaEnumerable)
            {
                foreach (SqlSchemaChange src in Family.SchemaUpdateChanges)
                {
                    NoChanges = false;
                    using (src)
                    {
                        // Check if the change has already been applied or not
                        if (CurrentChanges == null
                                    || CurrentChanges.Count == 0
                                    || !CurrentChanges.ContainsKey(src.Id))
                        {
                            // At least one change is being applied
                            ApplyingChange = true;

                            this.FireOnUpdate(DatabaseUpdateContext.Information, String.Format("Applying change '{0}' [{1}]", src.Description, src.Id));

                            // Open a transaction to make the schema changes
                            using (SqlTransaction trans = connection.BeginTransaction())
                            {
                                try
                                {
                                    if (src.Scripts != null)
                                    {
                                        // Execute each of the scripts
                                        foreach (SqlSource Script in src.Scripts)
                                        {
                                            if (Script != null)
                                            {
                                                FireOnUpdate(DatabaseUpdateContext.Schema, Script.Name);
                                                SqlHelper.ExecuteContent(Script, trans, this.CommandTimeOut);
                                            }
                                        }
                                    }

                                    // Create a schema change object
                                    SchemaManagement.SchemaChange c = new SchemaManagement.SchemaChange();
                                    c.FamilyName = Family.Name;
                                    c.FamilyId = Family.Id;
                                    c.ChangeId = src.Id;
                                    c.Description = src.Description;
                                    c.InstallId = installId;
                                    // Apply the version change information
                                    Schema.ApplySchemaChange(trans, c, this.CommandTimeOut);


                                    trans.Commit();
                                }
                                catch
                                {
                                    try
                                    {
                                        trans.Rollback();
                                    }
                                    catch
                                    {
                                        // Ignore
                                    }

                                    throw;
                                }
                            }
                        }
                        else
                        {
                            this.FireOnUpdate(DatabaseUpdateContext.Information, String.Format("Change '{0}' [{1}] has already been applied", src.Description, src.Id));
                        }
                    }
                }
            }

            if (NoChanges)
            {
                return true;
            }
            else
            {
                return ApplyingChange;
            }
        }

        private void CleanupSchema(IEnumerable<SqlSchemaChangeFamily> schemaEnumerable, Guid installId, SqlConnection connection)
        {
            foreach (SqlSchemaChangeFamily Family in schemaEnumerable)
            {
                if (Family.SchemaCleanupChanges != null)
                {
                    foreach (SqlSchemaChange src in Family.SchemaCleanupChanges)
                    {
                        using (src)
                        {
                            this.FireOnUpdate(DatabaseUpdateContext.Information, String.Format("Cleanup change '{0}' [{1}]", src.Description, src.Id));

                            try
                            {
                                if (src.Scripts != null)
                                {
                                    // Execute each of the scripts
                                    foreach (SqlSource Script in src.Scripts)
                                    {
                                        if (Script != null)
                                        {
                                            FireOnUpdate(DatabaseUpdateContext.Schema, Script.Name);
                                            SqlHelper.ExecuteContent(Script, connection, this.CommandTimeOut);
                                        }
                                    }
                                }
                            }
                            catch
                            {
                                throw;
                            }
                        }
                    }
                }
            }
        }

        // ==============================================================
        // Private replication functions that take a connection string
        // so that the update schema call can call them on the same
        // connection it is already using.
        // ==============================================================

        /// <summary>
        /// Install replication using the given connection
        /// </summary>
        /// <param name="Connection">The connection to use</param>
        private void InstallReplication(SqlConnection Connection, string SnapshotDirectory)
        {
            if (Connection == null)
                throw new ArgumentNullException("Connection", "No sql connection specified");

            SqlParameter Param = new SqlParameter("@working_directory", SqlDbType.NVarChar);
            Param.Value = SnapshotDirectory;

            SqlParameter[] Params = new SqlParameter[] { Param };

            Execute.ExecuteStoredProcedure(Connection, "sproc_db_InstallReplication", CommandTimeOut, Params);
        }

        /// <summary>
        /// Uninstall replication using the given connection
        /// </summary>
        /// <param name="Connection">The connection to use</param>
        private void UninstallReplication(SqlConnection Connection)
        {
            if (Connection == null)
                throw new ArgumentNullException("Connection", "No sql connection specified");

            Execute.ExecuteStoredProcedure(Connection, "sproc_db_UninstallReplication", CommandTimeOut);
        }

        /// <summary>
        /// Publish the replication using the given connection
        /// </summary>
        /// <param name="Connection">The connection to use</param>
        private void PublishReplication(SqlConnection Connection)
        {
            if (Connection == null)
                throw new ArgumentNullException("Connection", "No sql connection specified");

            FireOnUpdate(DatabaseUpdateContext.Publish, null);
            Execute.ExecuteStoredProcedure(Connection, "sproc_db_Publish", CommandTimeOut);
        }

        /// <summary>
        /// recover sql jobs for a specific database connection
        /// </summary>
        /// <param name="Connection"></param>
        private void RecoverSQLJobs(SqlConnection Connection)
        {
            if (Connection == null)
                throw new ArgumentNullException("Connection", "No sql connection specified");

            FireOnUpdate(DatabaseUpdateContext.Jobs, null);
            SQLJobDefinition[] sqlJobDefinitions = DBDataJobs.GetDBXMLOfSQLJob(Connection.Database, Connection);
            foreach (SQLJobDefinition sqlJobDefinition in sqlJobDefinitions)
            {
                Job.Generator.SQLJobGenerator sqlJobGenerator = new Job.Generator.SQLJobGenerator(sqlJobDefinition);
                string cmdText = sqlJobGenerator.GenerateSQL();
                Execute.ExecuteNonQuery(Connection, cmdText, 200);
                FireOnUpdate(DatabaseUpdateContext.Jobs, string.Format("Recovered Job [{0}] successfully.", sqlJobDefinition.JobName));
            }
        }
        /// <summary>
        /// remove sql jobs for a specific database connection
        /// </summary>
        /// <param name="Connection"></param>
        private void RemoveSQLJobs(SqlConnection Connection)
        {
            if (Connection == null)
                throw new ArgumentNullException("Connection", "No sql connection specified");

            FireOnUpdate(DatabaseUpdateContext.Jobs, null);
            SQLJobDefinition[] sqlJobDefinitions = DBDataJobs.GetDBXMLOfSQLJob(Connection.Database, Connection);
            foreach (SQLJobDefinition sqlJobDefinition in sqlJobDefinitions)
            {
                sqlJobDefinition.JobSteps = null;
                sqlJobDefinition.JobSchedules = null;
                sqlJobDefinition.Operation = OperationType.Delete;
                Job.Generator.SQLJobGenerator sqlJobGenerator = new Job.Generator.SQLJobGenerator(sqlJobDefinition);
                string cmdText = sqlJobGenerator.GenerateSQL();
                Execute.ExecuteNonQuery(Connection, cmdText, 200);
                FireOnUpdate(DatabaseUpdateContext.Jobs, string.Format("Removed Job [{0}] successfully.", sqlJobDefinition.JobName));
            }
        }
        /// <summary>
        /// Unpublish the replication using the given connection
        /// </summary>
        /// <param name="Connection">The connection to use</param>
        private void UnpublishReplication(SqlConnection Connection)
        {
            if (Connection == null)
                throw new ArgumentNullException("Connection", "No sql connection specified");

            FireOnUpdate(DatabaseUpdateContext.Unpublish, null);
            Execute.ExecuteStoredProcedure(Connection, "sproc_db_Unpublish", CommandTimeOut);
        }

        private void RegisterSlaveDB(string slaveName, string slaveServerNetBiosName, string slaveVersion, SqlConnection connection)
        {
            if (connection == null)
                throw new ArgumentNullException("Connection", "No sql connection specified");

            SqlParameter[] Parameters = new SqlParameter[3];
            Parameters[0] = new SqlParameter("@name", SqlDbType.VarChar);
            Parameters[0].Value = slaveName;
            Parameters[1] = new SqlParameter("@databaseServer", SqlDbType.VarChar);
            Parameters[1].Value = slaveServerNetBiosName;
            Parameters[2] = new SqlParameter("@slaveVersion", SqlDbType.VarChar);
            Parameters[2].Value = slaveVersion;

            Execute.ExecuteStoredProcedure(connection, "sproc_db_RegisterSlaveDB", CommandTimeOut, Parameters);
        }

        private void SetSlaveVersion(SlaveDBRegistration slaveRegistration, string slaveVersion, SqlConnection connection)
        {
            if (connection == null)
                throw new ArgumentNullException("Connection", "No sql connection specified");

            SqlParameter[] Parameters = new SqlParameter[3];
            Parameters[0] = new SqlParameter("@slaveId", SqlDbType.UniqueIdentifier);
            Parameters[0].Value = slaveRegistration.Id;
            Parameters[1] = new SqlParameter("@slaveName", SqlDbType.VarChar);
            Parameters[1].Value = slaveRegistration.Name;
            Parameters[2] = new SqlParameter("@slaveVersion", SqlDbType.VarChar);
            Parameters[2].Value = slaveVersion;

            Execute.ExecuteStoredProcedure(connection, "sproc_db_SetSlaveVersion", CommandTimeOut, Parameters);
        }


        private SlaveDBRegistration GetSlaveDBRegistration(string serviceGroupName, SqlConnection connection)
        {
            SlaveDBRegistration slaveRegistration = null;
            if (connection == null)
                throw new ArgumentNullException("Connection", "No sql connection specified");
            SqlDataReader Reader = null;
            try
            {
                SqlParameter[] Parameters = new SqlParameter[1];
                Parameters[0] = new SqlParameter("@slaveName", SqlDbType.VarChar);
                Parameters[0].Value = serviceGroupName;

                Reader = Execute.ExecuteReader(connection, "sproc_db_GetSlaveDBRegistration", CommandTimeOut, Parameters);
                if (Reader.Read())
                {
                    slaveRegistration = new SlaveDBRegistration();
                    //serviceGroupRegistration.BranchId = Reader.GetGuid(Reader.GetOrdinal("BranchId"));
                    //if (!(Reader.IsDBNull(Reader.GetOrdinal("BranchName"))))
                    //{
                    //    serviceGroupRegistration.MasterName = Reader.GetString(Reader.GetOrdinal("BranchName"));
                    //}
                    if (!(Reader.IsDBNull(Reader.GetOrdinal("Description"))))
                    {
                        slaveRegistration.Description = Reader.GetString(Reader.GetOrdinal("Description"));
                    }
                    slaveRegistration.Id = Reader.GetGuid(Reader.GetOrdinal("slaveId"));
                    slaveRegistration.Name = serviceGroupName;
                }
                return slaveRegistration;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (Reader != null)
                {
                    Reader.Close();
                }
            }
        }

        /// <summary>
        /// Subscribe the replication to the specified database server name and database name
        /// on the specified connection
        /// </summary>
        /// <param name="DatabaseServerName">The name of the subscription database server</param>
        /// <param name="Connection">The connection to use</param>
        private void SubscribeReplication(string DatabaseServerName, SqlConnection Connection)
        {
            if (Connection == null)
                throw new ArgumentNullException("Connection", "No sql connection specified");

            if (String.IsNullOrEmpty(DatabaseServerName))
                throw new ArgumentNullException("DatabaseServerName", "No database server specified");

            SqlParameter[] Parameters = new SqlParameter[1];
            Parameters[0] = new SqlParameter("@slaveServerName", SqlDbType.VarChar);
            Parameters[0].Value = DatabaseServerName;

            Execute.ExecuteStoredProcedure(Connection, "sproc_db_SubscribeSlaveDB", CommandTimeOut, Parameters);
        }

        /// <summary>
        /// Subscribe all the servicegroups within within the bm_servicegroup table
        /// </summary>
        /// <param name="Connection">The connection to use</param>
        private void ReSubscribeAll(SqlConnection Connection)
        {
            if (Connection == null)
                throw new ArgumentNullException("Connection", "No sql connection specified");

            Execute.ExecuteStoredProcedure(Connection, "sproc_db_ReSubscribeAllSlaves", CommandTimeOut);
        }


        /// <summary>
        /// Unsubscribe replication from the specified database server name 
        /// on the connection specified
        /// </summary>
        /// <param name="DatabaseServerName">The name of the subscription database server</param>
        /// <param name="Connection">The connection to use</param>
        private void UnsubscribeReplication(string DatabaseServerName, SqlConnection Connection)
        {
            SqlParameter[] Parameters = new SqlParameter[1];
            Parameters[0] = new SqlParameter("@subscriberServerName", SqlDbType.VarChar);
            Parameters[0].Value = DatabaseServerName;

            Execute.ExecuteStoredProcedure(Connection, "sproc_db_UnsubscribeSlaveDB", CommandTimeOut, Parameters);
        }

        /// <summary>
        /// Updates all of the user stored procedures
        /// </summary>
        /// <exception cref="Exceptions.SqlScriptException">Thrown if there is an sql script error</exception>
        /// <param name="Connection">The SQL connection to the database</param>
        /// <param name="StoredProcedureList">The Sources to be created and their permissions</param>
        private void UpdateStoredProcedures(SortedDictionary<string, StoredProcedureDefinition> StoredProcedureList, SqlConnection Connection)
        {
            foreach (StoredProcedureDefinition StoredProcedure in StoredProcedureList.Values)
            {
                using (StoredProcedure.sqlSource)
                {
                    // Open a transaction
                    using (SqlTransaction trans = Connection.BeginTransaction())
                    {
                        try
                        {
                            FireOnUpdate(DatabaseUpdateContext.StoredProcedures, StoredProcedure.sqlSource.Name);
                            SqlHelper.ExecuteContent(StoredProcedure.sqlSource, trans, this.CommandTimeOut);
                            trans.Commit();
                        }
                        catch (Exception)
                        {
                            try
                            {
                                if (trans != null)
                                {
                                    trans.Rollback();
                                }
                            }
                            catch
                            {
                                // Ignore
                            }
                            throw;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Updates all of the user functions
        /// </summary>
        /// <exception cref="Exceptions.SqlScriptException">Thrown if there is an sql script error</exception>
        /// <param name="DatabasePackage">The database package</param>
        private void UpdateSources(DatabaseUpdateContext context, IEnumerable<SqlSource> Sources, SqlConnection Connection)
        {
            foreach (SqlSource Source in Sources)
            {
                using (Source)
                {
                    // Open a transaction
                    using (SqlTransaction trans = Connection.BeginTransaction())
                    {
                        try
                        {
                            FireOnUpdate(context, Source.Name);
                            SqlHelper.ExecuteContent(Source, trans, this.CommandTimeOut);
                            trans.Commit();
                        }
                        catch (Exception)
                        {
                            try
                            {
                                trans.Rollback();
                            }
                            catch
                            {
                                // Ignore
                            }
                            throw;
                        }
                    }
                }
            }
        }

        private void WriteSqlSource(SqlSource source, TextWriter writer)
        {
            string cmd;
            while ((cmd = source.Content.ReadCommand()) != null)
            {
                writer.Write(cmd);
                writer.WriteLine("GO");
            }
        }

        private void WriteSourceContent(IEnumerable<SqlSource> Sources, System.IO.TextWriter writer)
        {
            foreach (SqlSource Source in Sources)
            {
                using (Source)
                {
                    WriteSqlSource(Source, writer);
                }
            }
        }

        private void WriteSchemaContent(IEnumerable<SqlSchemaChangeFamily> schemaEnumerable, System.IO.TextWriter writer)
        {
            foreach (SqlSchemaChangeFamily Family in schemaEnumerable)
            {
                foreach (SqlSchemaChange src in Family.SchemaUpdateChanges)
                {
                    using (src)
                    {
                        writer.WriteLine();
                        writer.WriteLine("if not exists (select SchemaChangeLogId from XBSchemaVersion.SchemaChangeLog where SchemaChangeLogId = '{0}')", src.Id.ToString());
                        writer.WriteLine("begin");
                        foreach (SqlSource source in src.Scripts)
                        {
                            using (source)
                            {
                                WriteSqlSource(source, writer);
                            }
                        }
                        writer.WriteLine("end");
                    }
                }
            }
        }

        /// <summary>
        /// Retrieve the list of files for the current database
        /// </summary>
        /// <returns></returns>
        private List<DatabaseFile> _GetDatabaseFiles()
        {
            List<DatabaseFile> _listOfFiles = new List<DatabaseFile>();
            try
            {
                if (String.IsNullOrEmpty(DatabaseName) == false
                    && String.IsNullOrEmpty(this._ConnectionString) == false)
                {
                    using (SqlConnection connection = new SqlConnection(this._ConnectionString))
                    {
                        SqlDataReader reader
                            = Execute.ExecuteReader(connection, DatabaseConstants.SQL_DATABASE_FILES, CommandTimeOut);

                        while (reader.Read())
                        {
                            DatabaseFile databaseFile
                                = new DatabaseFile(ServerName,
                                                    reader[DatabaseConstants.SQL_DATABASE_FILES_FIELD_NAME].ToString(),
                                                    reader[DatabaseConstants.SQL_DATABASE_FILES_FIELD_PHYSICAL_NAME].ToString(),
                                                    reader[DatabaseConstants.SQL_DATABASE_FILES_FIELD_STATE].ToString());

                            _listOfFiles.Add(databaseFile);
                        }
                    }
                }
                else
                {
                    FireOnUpdate(DatabaseUpdateContext.Information,
                        String.Format("unable to retrieve list of database files, Database Name={0}, ConnectionString={1}",
                                        DatabaseName,
                                        ConnectionString));
                }
            }
            catch (Exception)
            {
                FireOnUpdate(DatabaseUpdateContext.Information,
                    String.Format("The database is in suspect mode, Database Name={0}, ConnectionString={1}", DatabaseName, ConnectionString));
            }
            return _listOfFiles;
        }

        /// <summary>
        /// Check if any of the files in the list exists
        /// </summary>
        /// <param name="files"></param>
        /// <returns></returns>
        private bool _DatabaseFilesExist(List<DatabaseFile> files)
        {
            bool serverIsLocal = true;
            if (GetComputerNamePhysicalNetBIOS().ToLower() != System.Environment.MachineName.ToLower())
            {
                FireOnUpdate(DatabaseUpdateContext.Information,
                    String.Format("Server is not local - SQL Server value:{0} - Environment.MachineName:{1}",
                                        GetComputerNamePhysicalNetBIOS(),
                                        System.Environment.MachineName));

                serverIsLocal = false;
            }

            // Check files
            foreach (DatabaseFile file in files)
            {
                FireOnUpdate(DatabaseUpdateContext.Information,
                    String.Format("File to check: {0}", file.FullPath));

                string fileFullPath = file.FullPath;
                if (!serverIsLocal)
                {
                    //try to access the file through the admin share
                    fileFullPath = String.Format(@"\\{0}\{1}$\{2}",
                                                    GetComputerNamePhysicalNetBIOS(),
                                                    fileFullPath.Substring(0, 1),                        // Ex: "C"
                                                    fileFullPath.Substring(3, fileFullPath.Length - 3));   // Ex: "Program Files/..."

                    FireOnUpdate(DatabaseUpdateContext.Information,
                                    String.Format("Server is not local, will use the path {0}", fileFullPath));
                }

                try
                {
                    if (File.Exists(fileFullPath))
                    {
                        FireOnUpdate(DatabaseUpdateContext.Information,
                                        String.Format("File {0} exists", file.LogicalName));
                        return true;
                    }
                    else
                    {
                        FireOnUpdate(DatabaseUpdateContext.Information,
                            String.Format("File {0} does not exist", file.LogicalName));
                    }
                }
                catch (Exception e)
                {
                    FireOnUpdate(DatabaseUpdateContext.Information,
                        String.Format("Could not check if file {0} existed",
                                        file.LogicalName,
                                        e.ToString()));
                }
            }
            return false;
        }

        /// <summary>
        /// Execute the sql script against the master database
        /// </summary>
        /// <param name="sqlScript"></param>
        public void RunScriptAgainstMaster(string sqlScript)
        {
            using (SqlConnection connection = new SqlConnection(this.MasterDBConnectionString))
            {
                Execute.ExecuteNonQuery(connection, sqlScript, CommandTimeOut);
            }
        }
    }
}
